package db;

import interfaces.IFCustomer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import model.Customer;

public class CustomerDb implements IFCustomer {
	protected Connection con;

	public CustomerDb() {
		connectDb();
	}

	public void connectDb() {
		con = DbConnection.getInstance().getDBcon();
	}

	public void disconnectDb() {
		DbConnection.getInstance();
		DbConnection.closeConnection();
	}

	public ArrayList<Customer> getAllCustomers() throws Exception {
		String query = "SELECT * FROM Customer JOIN Location ON Customer.zipcode = Location.zipcode";
		ResultSet rs = null;
		ArrayList<Customer> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all customers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				boolean isCompany = false;
				isCompany = rs.getBoolean("isCompany") ? true : false;
				data.add(new Customer(Integer.parseInt(rs.getString("customerId")),rs.getString("name"),rs.getString("phoneNumber"),rs.getString("address"),rs.getString("country"),rs.getString("city"),rs.getString("zipcode"),isCompany));
				
			
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public Customer searchCustomer(String name, String phoneNumber, String address, String country, String city) throws Exception {
		String sqlSearchName = "name LIKE '%" + name + "%' ";
		String sqlSearchPhoneNumber = !phoneNumber.equals("") ? "AND phoneNumber LIKE '%" + phoneNumber + "%' " : "";
		String sqlSearchAddress = !address.equals("") ? " AND address LIKE '%" + address + "%' " : "";
		String sqlSearchCountry = !country.equals("") ? " AND country LIKE '%" + country + "%' " : "";
		String sqlSearchCity = !city.equals("") ? " AND city LIKE '%" + city + "%' " : "";
		
		String query = 
				"SELECT * FROM Customer " +
				"JOIN Location ON Customer.zipcode = Location.zipcode " +
				"WHERE " +
				sqlSearchName +
				sqlSearchPhoneNumber +
				sqlSearchAddress +
				sqlSearchCountry +
				sqlSearchCity ;
		ResultSet rs = null;
		ArrayList<Customer> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all customers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				boolean isCompany = rs.getBoolean("isCompany");
				data.add(new Customer(Integer.parseInt(rs.getString("customerId")),rs.getString("name"),rs.getString("phoneNumber"),rs.getString("address"),rs.getString("country"),rs.getString("city"),rs.getString("zipcode"), isCompany));
			
				
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		if (data.size() == 1)
		{ 
			return data.get(0);
		}
		else
		{
			return new Customer();
		}
	}
	@Override
	public ArrayList<Customer> searchCustomerList(String name, String phoneNumber, String address, String country, String city) throws Exception {
		String sqlSearchName = "name LIKE '%" + name + "%' ";
		String sqlSearchPhoneNumber = !phoneNumber.equals("") ? "AND phoneNumber LIKE '%" + phoneNumber + "%' " : "";
		String sqlSearchAddress = !address.equals("") ? " AND address LIKE '%" + address + "%' " : "";
		String sqlSearchCountry = !country.equals("") ? " AND country LIKE '%" + country + "%' " : "";
		String sqlSearchCity = !city.equals("") ? " AND city LIKE '%" + city + "%' " : "";
		
		String query = 
				"SELECT * FROM Customer " +
				"JOIN Location ON Customer.zipcode = Location.zipcode " +
				"WHERE " +
				sqlSearchName.toUpperCase() +
				sqlSearchPhoneNumber.toUpperCase() +
				sqlSearchAddress.toUpperCase() +
				sqlSearchCountry.toUpperCase() +
				sqlSearchCity.toUpperCase() ;
		ResultSet rs = null;
		ArrayList<Customer> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all customers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				boolean isCompany = rs.getBoolean("isCompany") ? true : false;
				data.add(new Customer(Integer.parseInt(rs.getString("customerId")),rs.getString("name"),rs.getString("phoneNumber"),rs.getString("address"),rs.getString("country"),rs.getString("city"),rs.getString("zipcode"), isCompany));
				
				
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public void insertCustomer(String name, String phoneNumber, String address,
			String country, String city, int zipcode, int isCompany) throws Exception {
		String sqlName = name;
		String sqlPhoneNumber = phoneNumber;
		String sqlAddress = address;
		String sqlCountry = country;
		String sqlCity = city;
		int sqlZipcode = zipcode;
		int sqlIsCompany = isCompany == 1 ? 1 : 0;
		String query = 
				"INSERT INTO Customer (name, address, phoneNumber, zipcode, isCompany)" +
				"VALUES ('" +
				sqlName.toUpperCase() + "','" +
				sqlAddress.toUpperCase() + "','" +
				sqlPhoneNumber.toUpperCase() + "','" +
				
				sqlZipcode + "','" +
				sqlIsCompany + "')";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Customer ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			//rs = stmt.executeQuery(query);
			
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
	}

	@Override
	public void removeCustomer(String name, String phoneNumber, String address,
			String country, String city, int zipcode, int isCompany)
			throws Exception {
		String sqlName = name;
		String sqlPhoneNumber = phoneNumber;
		String sqlAddress = address;
		String sqlCountry = country;
		String sqlCity = city;
		int sqlZipcode = zipcode;
		int sqlIsCompany = isCompany == 1 ? 1 : 0;
		int sqlId = searchCustomer(sqlName, sqlPhoneNumber, sqlAddress, sqlCountry, sqlCity).getCustomerId();
		System.out.println("ID  = " + sqlId);
		String query = 
				"DELETE FROM Customer " +
				"WHERE customerId='" + sqlId + "'";
		if(sqlId > 0)
		{
			System.out.println("QUERRY : " + query);
			try {
				System.out.println("Deleting Customer ...");
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				stmt.executeUpdate(query);
				//rs = stmt.executeQuery(query);
				
				stmt.close();
				System.out.println("SUCCESS: Querry executed!");
			}// end try
			catch (SQLException ex) {
				System.out.println("ERROR:" + ex.getMessage());
			}
			JOptionPane.showMessageDialog(null, "Customer deleted!");
		}
		else
		{
			JOptionPane.showMessageDialog(null, "The customer does not exist or there is more than one Customer with specified information.");
		}
		
	}
}
